PostgreSQL array 應用 計算多欄位平均

前言

ptt database版有一篇 計算多欄位平均 傳送門

測試資料

我將table 簡化,重點在於平均數計算,就使用一個table.

create table t231121a (
  sid int not null primary key
, chi int
, eng int
, math int
);

insert into t231121a
select n
     , 60 + floor(40*random())
     , 60 + floor(40*random())
     , 60 + floor(40*random())
  from generate_series(1, 5) as n;
  
select *
  from t231121a;

 sid | chi | eng | math 
-----+-----+-----+------
   1 |  65 |  98 |   66
   2 |  95 |  85 |   84
   3 |  85 |  71 |   81
   4 |  63 |  75 |   85
   5 |  87 |  84 |   70
(5 rows)

傳統方式

select sid, avg(score)
  from (select sid
             , chi as score
          from t231121a
         union all
        select sid
             , eng as score
          from t231121a
         union all
        select sid
             , math as score
          from t231121a
        ) a
 group by sid
 order by sid;

 sid |         avg         
-----+---------------------
   1 | 76.3333333333333333
   2 | 88.0000000000000000
   3 | 79.0000000000000000
   4 | 74.3333333333333333
   5 | 80.3333333333333333
(5 rows)

傳統方式的缺點在於需要做多次的存取掃描.

改變table 設計的方式

create table t231121b (
  sid int not null primary key
, sname text
);

create table t231121c (
  sjid int not null primary key
, sjname text
);

create table t231121d (
  sid int not null
, sjid int not null
, score int
, primary key (sid, sjid)
);

insert into t231121b values
(1, '小帥'),(2, '小美'),(3, '大壯'),
(4, '鐵柱'),(5, '老皮');

insert into t231121c values
(1, '國文'), (2, '英文'), (3, '數學');

insert into t231121d values
(1, 1, 65), (1, 2, 98), (1, 3, 66),
(2, 1, 95), (2, 2, 85), (2, 3, 84),
(3, 1, 85), (3, 2, 71), (3, 3, 81),
(4, 1, 63), (4, 2, 75), (4, 3, 85),
(5, 1, 87), (5, 2, 84), (5, 3, 70);

select avg(score)
  from t231121d
 group by sid
 order by sid;

         avg         
---------------------
 76.3333333333333333
 88.0000000000000000
 79.0000000000000000
 74.3333333333333333
 80.3333333333333333
(5 rows)

哪種方式較好?

第二種方式就很容易計算出平均.但是在實際運用中,第一種方式開一條龍的也不在少數.一來科目數量較為固定,變動的不頻繁.二來在平時的顯示或是報表,時常需要將各科目列出,若用第二種方式,又需要做直轉橫.轉成類似第一種方式的顯示.
所以在項目固定的情況下,採用第一種方式,例如每月的分數,放12個欄位,也不見得不好.
但是這種一條龍的,要做多欄位的計算,例如平均,總和,因為系統內建的函數是針對單一欄位的,這時候要做橫轉直.

Array 又再次登場

之前已經有幾篇關於Array的應用.這次我們再使用Array.

-- 使用array 結合的情況
select sid
     , array[chi, eng, math]
  from t231121a;

 sid |   array    
-----+------------
   1 | {65,98,66}
   2 | {95,85,84}
   3 | {85,71,81}
   4 | {63,75,85}
   5 | {87,84,70}
(5 rows)
-- 將array 再次展開
select sid
     , (select avg(score) from unnest(array[chi, eng, math]) as score)
  from t231121a;

 sid |         avg         
-----+---------------------
   1 | 76.3333333333333333
   2 | 88.0000000000000000
   3 | 79.0000000000000000
   4 | 74.3333333333333333
   5 | 80.3333333333333333
(5 rows)

可以看到這樣做方便計算多欄位,執行效率也較高,只需做一次掃描.

結語

在資料項目有限的情況下,可以善用array來減少table,提高效能.